
import 'dart:convert';

import 'package:im_app/im/util/ImCacheFactory.dart';
import 'package:im_app/im/util/MapUtil.dart';
import 'package:logger/logger.dart';
import 'package:sqflite/sqflite.dart';

import '../util/DateUtil.dart';
import '../pojo/ImPojo.dart';
import 'BaseDao.dart';
import 'ImGroupDao.dart';
import 'ImSessionDao.dart';
import 'ImUserDao.dart';

class ImMessageDao extends BaseDao {

  final _logger = Logger(printer: PrettyPrinter(),);

  Future<ImMessage?> getById(int id) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    List? list = await database?.rawQuery("select * from im_message where id = ?", [id]);
    if (list != null && list.isNotEmpty) {
      return _toImMessage(list[0]);
    }
    return null;
  }

  Future<List<ImMessage>> getBySessionId(int sessionId, DateTime startTime, int size) async {
    Database? database = getDatabase(); //await getReadOnlyDatabase(); //ImSingleton.getInstance()?.dataBase;
    List<Map<String, dynamic>>? list = await database?.rawQuery("select * from im_message where sessionId = ? and "
        "status in (1, 3, 11, 12) and serverReceiveTime > ? order by serverReceiveTime desc limit 0, $size",
        [sessionId, DateUtil.formatDate(startTime)]);
    if (list != null && list.isNotEmpty) {
      return list.map((map)=>_toImMessage(map)).toList();
    }
    return [];
  }

  Future<List<ImMessage>> search(int sessionId, String search, int size) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    List<Map<String, dynamic>>? list = await database?.rawQuery('''select * from im_message where sessionId = ? and 
      content like ? and status not in (9,11,12,30, 50, 60)  order by serverReceiveTime desc limit 0, $size''',
        [sessionId, '%$search%']);
    if (list != null && list.isNotEmpty) {
      return list.map((map)=>_toImMessage(map)).toList();
    }
    return [];
  }

  Future<List<ImMessage>> getByServerIdsAndUserId(Set<String> serverIds, String userId) async {
    if (serverIds.isEmpty) return [];
    String param = serverIds.map((e) => "\'$e\'").join(",");
    Database? database = getDatabase(); //await getReadOnlyDatabase(); //ImSingleton.getInstance()?.dataBase;
    var sql = "select * from im_message where serverId in ($param) and currentUserId = ?";
    List? list = await database?.rawQuery(sql, [userId]);
    if (list != null && list.isNotEmpty) {
      return list.map((map)=>_toImMessage(map)).toList();
    }
    return [];
  }

  Future<List<ImMessage>> getBySessionIdAndTypes(int sessionId, String? search, DateTime? startDate,
      List<ImMessageType> messageTypes, int size, bool isDesc) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    var args = <Object>[];
    String where = "";

    if (sessionId != null) {
      where +=  " and sessionId = ? ";
      args.add(sessionId);
    }
    if (messageTypes.isNotEmpty) {
      where += " and messageType in ('${messageTypes.map((m)=>m.name).join(',')}') ";
    }
    if (startDate != null) {
      if (isDesc) {
        where += " and serverReceiveTime <= ? ";
      } else {
        where += " and serverReceiveTime >= ? ";
      }
      args.add(DateUtil.formatDate(startDate)!);
    }
    if (search != null && search != '') {
      where += " and content like ?";
      args.add('%$search%');
    }

    String order = isDesc ? "order by serverId desc" : "order by serverId";
    String sql = "select * from im_message where 1=1 $where and status not in (9,11,12) $order limit 0, $size";
    List? list = await database?.rawQuery(sql, args);
    if (list != null && list.isNotEmpty) {
      List<ImMessage> messages = await list.map((map)=>_toImMessage(map)).toList();
      for (var message in messages) {
        message.fromImUser = await ImCacheFactory.getInstance().getImUser(message.fromUserId);
      }
      return messages;
    }
    return [];
  }

  Future<List<ImMessage>> getNewsBySessionId(int sessionId, DateTime? serverReceiveDate, int size) async {
    Database? database = getDatabase(); //await getReadOnlyDatabase(); //ImSingleton.getInstance()?.dataBase;
    var where = serverReceiveDate != null ? ' and serverReceiveTime >= ?' : '';
    var sql = '''select * from im_message where sessionId = ? and status in (1, 3, 11, 12) $where order by 
        serverReceiveTime asc limit 0, $size''';
    List<Map<String, dynamic>>? list = null;
    if (serverReceiveDate != null) {
      list = await database?.rawQuery(sql, [sessionId, DateUtil.formatDate(serverReceiveDate)]);
    } else {
      list = await database?.rawQuery(sql, [sessionId]);
    }

    List<ImMessage>? result = list?.map((e) => _toImMessage(e)).toList();
    return result??[];
  }

  Future<List<ImMessage>> getOldBySessionId(int sessionId, DateTime serverReceiveDate, int size) async {
    Database? database = getDatabase(); //await getReadOnlyDatabase(); //ImSingleton.getInstance()?.dataBase;
    var where = serverReceiveDate != null ? ' and serverReceiveTime <= ?' : '';
    var sql = "select * from im_message where sessionId = ? and status in (1, 3, 11, 12) $where order by "
        "serverReceiveTime desc limit 0, $size";
    List<Map<String, dynamic>>? list = null;
    if (serverReceiveDate != null) {
      list = await database?.rawQuery(sql, [sessionId, DateUtil.formatDate(serverReceiveDate)]);
    } else {
      list = await database?.rawQuery(sql, [sessionId]);
    }
    List<ImMessage>? result = list?.map((e) => _toImMessage(e)).toList();
    return result == null ? [] : result;
  }

  Future<List<String>> getDays(int sessionId, DateTime startDate, int size) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    var where = startDate != null ? ' and serverReceiveTime <= ?' : '';
    var sql = "select distinct strftime('%Y-%m-%d', serverReceiveTime) as day from im_message where sessionId = ? and "
        "status in (1, 3, 20) $where order by serverReceiveTime desc limit 0, $size";
    var params = <Object>[sessionId];
    if (startDate != null) {
      var startDateStr = DateUtil.formatDate(startDate);
      params.add(startDateStr!);
    }
    List<Map<String, dynamic>>? list = await database?.rawQuery(sql, params);
    if (list != null && list.isNotEmpty) {
      return list.map((map)=>_toDayStr(map)).toList();
    }
    return [];
  }

  Future<List<ImMessage>> getNewMessages(int size) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    List<Map<String, dynamic>>? list = await database?.rawQuery("select * from im_message where status in (1, 3, 20) "
        "order by serverReceiveTime desc limit 0, $size", []);
    if (list != null && list.isNotEmpty) {
      return list.map((map)=>_toImMessage(map)).toList();
    }
    return [];
  }

  Future<ImMessage?> getByServerIdAndUserId(String serverId, String userId) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    List? list = await database?.rawQuery("select * from im_message where serverId = ? and currentUserId = ?",
        [serverId, userId]);
    if (list != null && list.isNotEmpty) {
      return Future.value(_toImMessage(list[0]));
    }
    return null;
  }

  Future<void> loadMessageOtherInfo(List<ImMessage> messages) async {
    for (var message in messages) {
      await loadSessionInfo(message!);
      await loadGroupInfo(message!);
      await loadRefMessage(message);
    }
  }

  Future<void> loadRefMessage(ImMessage message) async {
    var userDao = ImUserDao();
    if (message.refMessageIds != null) {
      var refIds = message.refMessageIds!.split(",");
      var refMessages = await getByServerIdsAndUserId(refIds.toSet(), message.currentUserId);
      message!.refMessages = refMessages;
      for (var refMessage in refMessages??[]) {
        refMessage.fromImUser = await userDao.getByUserId(refMessage.fromUserId);
        if (refMessage.refMessageIds != null) {
          await this.loadRefMessage(refMessage);
        }
      }
    }
  }

  Future<void> loadSessionInfo(ImMessage message) async{
    var userDao = ImUserDao();
    var sessionDao = ImSessionDao();

    message!.fromImUser = (await userDao.getByUserId(message.fromUserId));
    if (message.groupCode != null) {
      message!.imSession = (await sessionDao.getByGroupCodeAndUserId(message.groupCode!, message.currentUserId));
    } else {
      message!.imSession = (await sessionDao.getByUserId(message.currentUserId, message.fromUserId, message.toUserId!));
    }
  }

  Future<void> loadGroupInfo(ImMessage message) async {
    var groupDao = ImGroupDao();
    if (message.groupCode != null) {
      var imSession = message.imSession!;
      var group = await groupDao.getByCodeAndUserId(message.groupCode!, message.currentUserId);
      imSession!.group = group!;
      var currentGroupUser = group!.currentGroupUser;
      if (currentGroupUser!.userName == null) {
        currentGroupUser!.userName = message!.fromImUser!.userName;
      }
      message.fromGroupUser = currentGroupUser;
    }
  }

  Future<List<ImMessage>> getByUserIds(String currentUserId, List<String> p2pCodeList) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    String param = p2pCodeList.map((e) => "\"$e\"").join(",");
    String sql = "select * from im_message where currentUserId = ? and (toUserId in ($param) or fromUserId in ($param))";
    List<Map<String, Object?>>? list = await database?.rawQuery(sql, [currentUserId]);
    return list != null ? list.map((map)=>_toImMessage(map)).toList() : [];
  }

  Future<List<ImMessage>> getMessagesByGroupIds(String currentUserId,
      List<String> p2gCodeList) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    String param = p2gCodeList.map((e) => "\"$e\"").join(",");
    String sql = "select * from im_message where currentUserId = ? and groupId in ($param)";
    List<Map<String, Object?>>? list = await database?.rawQuery(sql, [currentUserId]);
    List<ImMessage>? result = list?.map((e) => _toImMessage(e)).toList();
    return result != null ? result : [];
  }

  Future<List<ImMessage>> getNeedSynActionStatusMessagesByUserId(String userId, int pageSize) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    String sql = "select * from im_message where currentUserId = ? and actionStatus in (1, 6) and status "
        "not in (3,9,12,30) order by id limit 0, $pageSize";
    List<Map<String, Object?>>? list = await database?.rawQuery(sql, [userId]);
    List<ImMessage>? result = list?.map((e) => _toImMessage(e)).toList();
    return result??[];
  }

  Future<void> batchInserts(List<ImMessage> messageList) async {
    if (messageList.isEmpty) return;
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    if (messageList.length == 1) {
      var data = _toInsertValueMap(messageList[0]);
      var id = await database?.insert("im_message", data);
      messageList[0].id = id;
      return;
    }
    Batch? batch = database?.batch();
    for (var message in messageList) {
      var data = _toInsertValueMap(message);
      batch?.insert("im_message", data);
    }
    List<Object?>? list = await batch?.commit();
    if (list?.length != messageList.length) {
      throw Exception("db batch insert message error");
    }
    var idx = 0;
    for (var id in list!) {
      messageList[idx++].id = id as int;
    }
    _logger.i("batch insertMessages json: ${jsonEncode(list)}");
  }

  Future<void> batchDeletes(List<ImMessage> messageList) async {
    if (messageList.isEmpty) return;
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    Batch? batch = database?.batch();
    for (var message in messageList) {
      int id = message.id!;
      batch?.rawDelete('delete from im_message where id = ?', [id]);
    }
    List<Object?>? list = await batch?.commit();
    _logger.i("batch message delete json: ${list?.length}");
  }

  Future<void> cleanMessages(String userId, List<int> messageIds) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    Batch? batch = database?.batch();
    for (var id in messageIds) {
      batch?.delete("im_message", where: "currentUserId = ? and id=?", whereArgs:[userId, id]);
    }
    List<Object?>? list = await batch?.commit();
    _logger.i('db clean json: ${jsonEncode(list)}');
  }

  Future<void> batchUpdates(String currentUserId, List<ImMessage> messageList) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    Batch? batch = database?.batch();
    for (var message in messageList) {
      var data = _toUpdateValueMap(message);

      batch?.update("im_message", data, where: "currentUserId = ? and id=?",
          whereArgs:[message.currentUserId, message.id]);
    }
    List<Object?>? list = await batch?.commit();
    _logger.i('batch updateMessages json: ${jsonEncode(list)}');
  }

  Future<void> batchUpdateExts(String currentUserId, List<ImMessage> messageList) async {
    if (messageList.isEmpty) return;
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    Batch? batch = database?.batch();
    for (var message in messageList) {
      var data = _toUpdateValueExtMap(message);

      batch?.update("im_message", data, where: "currentUserId = ? and id=?",
          whereArgs:[message.currentUserId, message.id]);
    }
    List<Object?>? list = await batch?.commit();
    _logger.i('batch updateExts json: ${jsonEncode(list)}');
  }

  Future<void> batchChangeMessageStatus(List<ImMessage> messages) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    Batch? batch = database?.batch();
    for (var message in messages) {
      var data = _toUpdateStatusValueMap(message);

      batch?.update("im_message", data, where: "id = ?", whereArgs:[message.id]);
    }
    List<Object?>? list = await batch?.commit();
    _logger.i('batch updateMessages json: ${jsonEncode(list)}');
  }

  Future<void> changeMessageActionStatus(Set<int> ids, ImMessageActionStatus messageStatus) async {
    if (ids.isEmpty) return;
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    Batch? batch = database?.batch();
    for (var id in ids) {
      var data = {'actionStatus': messageStatus.value, 'updateTime': DateUtil.formatDate(DateTime.now())};

      batch?.update("im_message", data, where: "id = ?", whereArgs:[id]);
    }
    List<Object?>? list = await batch?.commit();
    _logger.i('changeMessageActionStatus json: ${jsonEncode(list)}');
  }

  Future<void> changeMessageActionStatus2(String userId, Set<String> serverIds, ImMessageActionStatus messageStatus) async {
    if (serverIds.isEmpty) return;
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    Batch? batch = database?.batch();
    for (var serverId in serverIds) {
      var data = {'actionStatus': messageStatus.value, 'updateTime': DateUtil.formatDate(DateTime.now())};

      batch?.update("im_message", data, where: "currentUserId = ? and serverId = ? ", whereArgs:[userId, serverId]);
    }
    List<Object?>? list = await batch?.commit();
    _logger.i('changeMessageActionStatus json: ${jsonEncode(list)}');
  }

  Future<void> updateMessageStatus(int messageId, ImMessageStatus messageStatus, ImMessageActionStatus actionStatus) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    await database?.update("im_message", _toUpdateStatusValueMap2(messageStatus, actionStatus), where: "id = ?",
        whereArgs: [messageId]);
  }

  Future<void> changeMessageFileStatus(int id, ImMessageFileStatus messageStatus) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    var data = {'fileStatus': messageStatus.value, 'updateTime': DateUtil.formatDate(DateTime.now())};

    database?.update("im_message", data, where: "id = ?", whereArgs:[id]);
    _logger.i('changeMessageFileStatus id: $id');
  }

  Future<List<ImMessage>> getNeedSendMessagesByUserId(String userId, int size) async {
    Database? database = getDatabase(); //ImSingleton.getInstance()?.dataBase;
    String sql = '''select * from im_message where fromUserId = ? and (status in (1) or fileStatus in (1)
        or (fileStatus is null and messageType in ('Image', 'File', 'Stream', 'Audio') and status = 3))  and status != 9
        order by id limit 0, $size''';
    List<Map<String, Object?>>? list = await database?.rawQuery(sql, [userId]);
    List<ImMessage>? result = list?.map((e) => _toImMessage(e)).toList();
    return result != null ? result : [];
  }

  Future<List<ImHotMessageTotal>> getHotMessageTotal(String userId, DateTime startDate) async {
    Database? database = getDatabase();
    var status = [ImMessageStatus.created, ImMessageStatus.sended].join(',');
    var actionStatus = [ImMessageActionStatus.none].join(',');
    var sql = '''select m.sessionId sessionId, count(*) total from im_message m where m.currentUserId = ? and 
        m.status in (1, 3) and serverReceiveTime > ? and (m.actionStatus in(0) or m.actionStatus is null) and
        ((m.toUserId = ? and m.fromUserId != ?) or (m.groupCode is not null and m.fromUserId != ?)) 
        and m.sessionId is not null group by m.sessionId''';
    List<Map<String, dynamic>>? list = await database?.rawQuery(sql, [userId, DateUtil.formatDate(startDate), userId, userId, userId]);
    if (list != null) {
      List<ImHotMessageTotal> result = list!.map((e) => _toImHotMessageTotal(e)).toList();
      return result;
    } else {
      return [];
    }
  }

  Map<String, Object?> _toInsertValueMap(ImMessage imMessage) {
    Map<String, Object?> map = {};
    map['currentUserId'] = imMessage.currentUserId;
    map['clientId'] = imMessage.clientId;
    map['serverId'] = imMessage.serverId;
    map['fromUserId'] = imMessage.fromUserId;
    map['sessionId'] = imMessage.sessionId;
    map['content'] = imMessage.content;
    map["status"]= imMessage.status?.value;
    map['groupCode'] = imMessage.groupCode;
    map['toUserId'] = imMessage.toUserId;
    map['messageType'] = imMessage.messageType.name;
    map['version'] = imMessage.version;
    map['refType'] = imMessage.refType?.value;
    map['refMessageIds'] = imMessage.refMessageIds;
    map['ext'] = imMessage.ext;
    map['ext1'] = imMessage.ext1;
    map['ext2'] = imMessage.ext2;
    map['fileStatus'] = imMessage.fileStatus?.value;
    map['serverReceiveTime'] = DateUtil.formatDate(imMessage.serverReceiveTime);
    map['createTime'] = DateUtil.formatDate(imMessage.createTime);
    map['updateTime'] = DateUtil.formatDate(imMessage.updateTime);

    return map;
  }

  Map<String, Object?> _toUpdateValueMap(ImMessage imMessage) {
    Map<String, Object?> map = {};
    map['serverId'] = imMessage.serverId;
    map['content'] = imMessage.content;
    map['ext'] = imMessage.ext;
    map['ext1'] = imMessage.ext1;
    map['ext2'] = imMessage.ext2;
    map["status"]= imMessage.status?.value;
    map['actionStatus'] = imMessage.actionStatus?.value;
    map['fileStatus'] = imMessage.fileStatus?.value;
    map['serverReceiveTime'] = DateUtil.formatDate(imMessage.serverReceiveTime);
    map['updateTime'] = DateUtil.formatDate(imMessage.updateTime);

    return map;
  }

  Map<String, Object?> _toUpdateValueExtMap(ImMessage imMessage) {
    Map<String, Object?> map = {};
    map['ext'] = imMessage.ext;

    return map;
  }

  Map<String, Object?> _toUpdateStatusValueMap2(ImMessageStatus status, ImMessageActionStatus actionStatus) {
    Map<String, Object?> map = {};
    map["status"]= status.value;
    map['actionStatus'] = actionStatus.value;
    map['updateTime'] = DateUtil.formatDate(DateTime.now());

    return map;
  }

  Map<String, Object?> _toUpdateStatusValueMap(ImMessage imMessage) {
    Map<String, Object?> map = {};
    map['serverId'] = imMessage.serverId;
    map["status"]= imMessage.status?.value;
    map['actionStatus'] = imMessage.actionStatus?.value;
    map['serverReceiveTime'] = DateUtil.formatDate(imMessage.serverReceiveTime);
    map['updateTime'] = DateUtil.formatDate(imMessage.updateTime);

    return map;
  }

  ImMessage _toImMessage(Map<String, Object?> map) {
    String fromUserId = map['fromUserId'] as String;
    String content = map['content'] as String;
    ImMessageType messageType = ImMessageType.ofName(map['messageType'] as String);
    ImMessage dbMessage = ImMessage(fromUserId: fromUserId, content: content, messageType: messageType);
    dbMessage.id = MapUtil.getInt(map, 'id');
    dbMessage.clientId = MapUtil.getString(map, 'clientId');
    dbMessage.currentUserId = MapUtil.getString(map, 'currentUserId');
    dbMessage.groupCode = MapUtil.getStringOrNull(map, "groupCode");
    dbMessage.toUserId = MapUtil.getStringOrNull(map, 'toUserId');
    dbMessage.serverId = MapUtil.getStringOrNull(map, "serverId");
    dbMessage.sessionId = MapUtil.getIntOrNull(map, "sessionId");
    dbMessage.createTime = MapUtil.getDate(map, "createTime");
    dbMessage.updateTime = MapUtil.getDate(map, "updateTime");
    dbMessage.serverReceiveTime = MapUtil.getDateOrNull(map, 'serverReceiveTime');
    dbMessage.status = ImMessageStatus.ofValue(MapUtil.getInt(map, "status"));
    dbMessage.fileStatus = ImMessageFileStatus.ofValue(MapUtil.getIntOrNull(map, "fileStatus"));
    dbMessage.actionStatus = ImMessageActionStatus.ofValue(MapUtil.getIntOrNull(map, "actionStatus"));
    dbMessage.refType = ImMessageRefType.ofValue(MapUtil.getIntOrNull(map, "refType"));
    dbMessage.refMessageIds = MapUtil.getStringOrNull(map, "refMessageIds");
    dbMessage.ext = MapUtil.getStringOrNull(map, "ext");
    dbMessage.ext1 = MapUtil.getStringOrNull(map, "ext1");
    dbMessage.ext2 = MapUtil.getStringOrNull(map, "ext2");
    dbMessage.version = MapUtil.getInt(map, "version");
    return dbMessage;
  }

  String _toDayStr(Map<String, Object?> map) {
    return MapUtil.getString(map, 'day');
  }

  ImHotMessageTotal _toImHotMessageTotal(Map<String, Object?> map) {
    var sessionId = MapUtil.getInt(map, "sessionId");
    var total = MapUtil.getInt(map, "total");
    var hotMessageTotal = ImHotMessageTotal(sessionId: sessionId, total: total);
    return hotMessageTotal;
  }
}